package com.eascs.web.o2o.web.reward.service.impl;

import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.stereotype.Service;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.web.reward.dao.BaseDao;
import com.eascs.web.o2o.web.reward.entity.PersonalInfo;
import com.eascs.web.o2o.web.reward.entity.RewardDataDic;
import com.eascs.web.o2o.web.reward.entity.RewardItem;
import com.eascs.web.o2o.web.reward.entity.SearchStaffForm;
import com.eascs.web.o2o.web.reward.service.BaseService;
import com.eascs.workflow.common.vo.ModelResult;

@Service
public class BaseServiceImpl implements BaseService{

	private static Logger logger = LoggerFactory.getLogger(BaseServiceImpl.class);
	@Inject
	BaseDao baseDao;
	
	@Override
	public <T> List<T> findAllEntity(Class<T> returnType){
		return baseDao.findAllEntity(returnType);
	}

	@Override
	public List<RewardItem> findRewardItems(String dd_no) {
		String sql = "select * from dim_reward_pub_dditem as d where d.dd_no = ?";
		return baseDao.findEntitys(sql, RewardItem.class, dd_no);
	}

	@Override
	public List<RewardDataDic> findRewardDataDics() {
		String sql = "select * from dim_reward_pub_datadictionary as d where d.dd_type = 'REWARD_TYPE'";
		return baseDao.findEntitys(sql, RewardDataDic.class);
	}

	@Override
	public PageResult<Object[]> findPersonalInfo(SearchStaffForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t.t_month , t.user_name, t.province_name , t.join_date , "
				+ " h.`dd_name` , t.province_echelon , t.start_royalty_point , "
				+ " t.evaluate_coefficient , s.item_name , t.user_no , "
				+ " t.is_lock FROM dim_reward_personal_info t "
				+ " LEFT JOIN dim_reward_pub_dditem s ON "
				+ " t.evaluate_scheme=s.item_no LEFT JOIN dim_reward_pub_datadictionary "
				+ " h ON h.`dd_no` = t.evaluate_type where 1=1 ");
		
		if(!StringUtil.isEmpty(form.getTmonth())){
			sb.append(" and t.t_month like ?");
			sqlPageQuery.addLikeParam(form.getTmonth());
			}
		if(!StringUtil.isEmpty(form.getUserName())){
			sb.append(" and t.user_name like ?");
			sqlPageQuery.addLikeParam(form.getUserName());
		}
		if(!StringUtil.isEmpty(form.getEvaluateType())){
			sb.append(" and t.evaluate_type = ?");
			sqlPageQuery.addParam(form.getEvaluateType());
		}
		if(!StringUtil.isEmpty(form.getUserNo())){
			sb.append(" and t.user_no = ?");
			sqlPageQuery.addParam(form.getUserNo());
		}
		if(!StringUtil.isEmpty(form.getProvince())){
			sb.append(" and t.province_code = ?");
			sqlPageQuery.addParam(form.getProvince());
		}
		if(!StringUtil.isEmpty(form.getStartDate())){
			sb.append(" and t.join_date > ?");
			sqlPageQuery.addParam(form.getStartDate());
		}
		if(!StringUtil.isEmpty(form.getEndDate())){
			sb.append(" and t.join_date < ?");
			sqlPageQuery.addParam(form.getEndDate());
		}
		sqlPageQuery.setQuerySql(sb.toString());
		logger.debug("sql:{}", sb.toString());
		return baseDao.findDataWithPage(sqlPageQuery);
	}

	@Override
	public int delPersionInfo(PersonalInfo pi) {
		return baseDao.deleteEntitys(pi);
	}

	@Override
	public boolean editAndSavePersonalInfo(PersonalInfo pi) {
		StringBuilder sb = new StringBuilder();
		sb.append("update dim_reward_personal_info d set ");
		
		if(!StringUtil.isEmpty(pi.getProvinceEchelon())){
			sb.append(" d.province_echelon = '" + pi.getProvinceEchelon() + "' ,");
		}
		if(!StringUtil.isEmpty(pi.getStartRoyaltyPoint())){
			sb.append(" d.start_royalty_point = '"+pi.getStartRoyaltyPoint()+"' ,");
		}
		
		//当月考核系数
		if(!StringUtil.isEmpty(pi.getEvaluateCoefficient())){
			sb.append(" d.evaluate_coefficient = '"+pi.getEvaluateCoefficient()+"' ,");
		}
		
		//考核类型,必填
		if(!StringUtil.isEmpty(pi.getEvaluateType())){
			sb.append(" d.evaluate_type = '"+pi.getEvaluateType()+"' ,");
		}
		//考核方案,必填
		if(!StringUtil.isEmpty(pi.getEvaluateScheme())){
			sb.append(" d.evaluate_scheme = '"+pi.getEvaluateScheme()+"' , ");
		}
		if(pi.getProvinceName() != null){
			sb.append(" d.province_code = '" + pi.getProvinceCode() + "' , ");
			sb.append(" d.province_name = '" + pi.getProvinceName() + "' , ");
		}
		sb.append(" d.user_code = '" + pi.getUserCode() + "' , ");
		sb.append(" d.update_user = '" + pi.getUpdateUser() + "' , ");
		sb.append(" d.update_date = '" + pi.getUpdate_date() + "'  ");
		
		sb.append(" where d.user_no = '"+pi.getUserNo()+"'");
		sb.append(" and d.is_lock != 'Y'");
		
		logger.debug("update sql:{}", sb.toString());	
		return baseDao.executeSql(sb.toString());
	}

	@Override
	public ModelResult<List<Object[]>> findListByLikeUserName(String userName) {
		String sql = "select d.employee_no , d.employee_name , d.province_name , "
				+ " d.join_date ,  d.t_month , p.province_code , d.employee_code , "
				+ " d.post_code , d.post_name , d.employee_status , d.is_dimission "
				+ " from dim_reward_employee_info d "
				+ " left join  dim_reward_province p on p.province_name = d.province_name "
				+ " where d.employee_name like '%" + userName + "%' and d.employee_no not in  "
				+ " (select t.user_no from dim_reward_personal_info t)";
		       
		ModelResult<List<Object[]>> modelResult = new ModelResult<>();
		try {
			List<Object[]> objects = baseDao.executeSqlForObjectArray(sql);
			modelResult.withModel(objects);
		} catch (Exception e) {
			logger.error("平台人员信息联想查询失败", e.getMessage());
			modelResult.withError("BaseServiceImpl.getListByLikeUserName", "平台人员信息联想查询失败:" + e.getMessage());
			e.printStackTrace();
		}
		return modelResult;
	}

	@Override
	public boolean addAndSavePersonalInfo(PersonalInfo pi) {
		return baseDao.saveEntity(pi);
	}

	@Override
	public boolean editAndConfirmPersonalInfo(PersonalInfo pi) {
		StringBuffer sb = new StringBuffer();
		sb.append("update dim_reward_personal_info d ");
		sb.append(" set d.is_lock = 'Y' , d.confirm_user = '").append(pi.getConfirmUser()).append("' , ");
		sb.append(" d.province_code = '").append(pi.getProvinceCode()).append("' , ");
		sb.append(" d.province_name = '").append(pi.getProvinceName()).append("' , ");
		sb.append(" d.join_date = '").append(pi.getJoinDate()).append("' , ");
		sb.append(" d.t_month = '").append(pi.getAssesMonth()).append("' , ");
		sb.append(" d.province_echelon = '").append(pi.getProvinceEchelon()).append("' , ");
		sb.append(" d.start_royalty_point = '").append(pi.getStartRoyaltyPoint()).append("' , ");
		sb.append(" d.evaluate_type = '").append(pi.getEvaluateType()).append("' , ");
		sb.append(" d.evaluate_scheme = '").append(pi.getEvaluateScheme()).append("' , ");
		sb.append(" d.evaluate_coefficient = '").append(pi.getEvaluateCoefficient()).append("' ,");
		sb.append(" d.confirm_date = '").append(pi.getConfirmDate()).append("' ");
		sb.append(" where d.user_no = '").append(pi.getUserNo()).append("'");
		return baseDao.executeSql(sb.toString());
	}


	@Override
	public List<Object[]> findPersonalByUserNo(String userNo,String tmonth) {
		String sql = "SELECT t.user_no, t.user_name, h.dd_no, h.dd_name, "
				+ " s.item_no , s.item_name FROM dim_reward_personal_info t  "
				+ " LEFT JOIN  dim_reward_pub_datadictionary h  "
				+ " ON t.evaluate_type=h.dd_no LEFT JOIN  dim_reward_pub_dditem s  "
				+ " ON  h.dd_no=s.dd_no WHERE t.user_no = '" + userNo + "' and t.t_month='"+tmonth+"'; ";
		List<Object[]> list = baseDao.executeSqlForObjectArray(sql);
		return list;
	}

	@Override
	public PersonalInfo findPersonalInfo(String userNo,String tmonth) {
		String sql = "select * from dim_reward_personal_info d"
				+ " where d.user_no = '"+userNo+"' and d.t_month='"+tmonth+"'";
		return baseDao.findPersonalInfo(sql);
	}
	
	

	@Override
	public String getEvaluateScheme(String ty, String province, Date dt) {
	    int v_interval_month;
	    String v_evaluate_scheme=null; 
	    String sql="select timestampdiff(month,'"+dt+"',concat(date_format(now(),'%Y-%m'),'-15'))";
	    String result = baseDao.findString(sql);
	    v_interval_month= Integer.parseInt(result);
	    logger.info("ty:{},province:{},dt:{},v_interval_month:{}", ty , province , dt , v_interval_month);
	    // 信贷经理保护期内
	    if ("10000".equals(ty) && v_interval_month<=3){
	    	 v_evaluate_scheme="1001";
	    }
	    //信贷经理保护期后
	   	else if("10000".equals(ty) && v_interval_month>3){
	   		v_evaluate_scheme="1002";	
	    }
	    //区域总监第一个保护期
		else if("10010".equals(ty) && v_interval_month<=3){
	   		v_evaluate_scheme="1003";	
	    }  
	    //区域总监第二个保护期
		else if("10010".equals(ty) && v_interval_month>3 && v_interval_month<=6){
	   		v_evaluate_scheme="1004";	
	    }
	    //区域总监保护期后
	  	else if("10010".equals(ty) && v_interval_month>6){
	  	   	v_evaluate_scheme="1005";	
	  	}
	    
	    //城市总监第一个保护期
		else if("10020".equals(ty) && v_interval_month<=3){
	   		v_evaluate_scheme="1006";	
	    }  
	    //城市总监第二个保护期
		else if("10020".equals(ty) && v_interval_month>3 && v_interval_month<=6){
	   		v_evaluate_scheme="1007";	
	    }
	    //城市总监保护期后
	  	else if("10020".equals(ty) && v_interval_month>6){
	  	   	v_evaluate_scheme="1008";	
	  	}
	    
	     
	    //老省区金融总保护期内
	  	else if("10030".equals(ty) && v_interval_month<=3 && !"hebei".equals(province) ){
	  	   	v_evaluate_scheme="1009";	
	  	}    
	    //老省区金融总保护期后
  	    else if("10030".equals(ty) && v_interval_month>3 && !"hebei".equals(province) ){
  	   		v_evaluate_scheme="1010";	
  	    }      
	
	    //新省区金融总保护期内
	  	else if("10030".equals(ty) && v_interval_month<=6 && "hebei".equals(province) ){
	  	   	v_evaluate_scheme="1011";	
	  	}    
        //新省区金融总保护期后
		else if("10030".equals(ty) && v_interval_month>6 && "hebei".equals(province) ){
		    v_evaluate_scheme="1012";	
		}  
	    //省区风险总  
		else if("10040".equals(ty)){
			v_evaluate_scheme="1013";	
	    }
	 
	    //省区综合总  
	   	else if("10050".equals(ty)){
	   		v_evaluate_scheme="1014";	
   	    }   
	    
	    //省区风险经理  
	   	else if("10060".equals(ty)){
	   		v_evaluate_scheme="1015";	
   	    }   	
	   	else v_evaluate_scheme="0";	
	    
	    return v_evaluate_scheme;
	}

	@Override
	public PageResult<Object[]> downloadPersonalInfo(SearchStaffForm form) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, form.getPageSize(), form.getPageIndex());
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t.t_month , t.province_name , t.user_code,t.user_name, t.post_name, t.join_date ,"
				+ " CASE t.is_dimission WHEN 0 THEN '在职' WHEN 1 THEN '离职'WHEN 2 THEN '锁定'END AS is_dimission, "
				+ " h.`dd_name`,s.item_name, t.province_echelon, t.start_royalty_point, t.evaluate_coefficient "
				+ " FROM dim_reward_personal_info t "
				+ " LEFT JOIN dim_reward_pub_dditem s ON t.evaluate_scheme=s.item_no "
				+ " LEFT JOIN dim_reward_pub_datadictionary h ON h.`dd_no` = t.evaluate_type WHERE 1=1 ");
		
		if(!StringUtil.isEmpty(form.getTmonth())){
			sb.append(" and t.t_month like ?");
			sqlPageQuery.addLikeParam(form.getTmonth());
			}
		if(!StringUtil.isEmpty(form.getUserName())){
			sb.append(" and t.user_name like ?");
			sqlPageQuery.addLikeParam(form.getUserName());
		}
		if(!StringUtil.isEmpty(form.getEvaluateType())){
			sb.append(" and t.evaluate_type = ?");
			sqlPageQuery.addParam(form.getEvaluateType());
		}
		if(!StringUtil.isEmpty(form.getUserNo())){
			sb.append(" and t.user_no = ?");
			sqlPageQuery.addParam(form.getUserNo());
		}
		if(!StringUtil.isEmpty(form.getProvince())){
			sb.append(" and t.province_code = ?");
			sqlPageQuery.addParam(form.getProvince());
		}
		if(!StringUtil.isEmpty(form.getStartDate())){
			sb.append(" and t.join_date > ?");
			sqlPageQuery.addParam(form.getStartDate());
		}
		if(!StringUtil.isEmpty(form.getEndDate())){
			sb.append(" and t.join_date < ?");
			sqlPageQuery.addParam(form.getEndDate());
		}
		sqlPageQuery.setQuerySql(sb.toString());
		logger.debug("sql:{}", sb.toString());
		return baseDao.findDataWithPage(sqlPageQuery);
	}
	
}
